/*
 * Clase cIfxCnx_DBGeneral para la conexion con la BD local (Mysql)
 * contiene los metodos para insertar, consultar y actualizar 
 * datos y todo lo referente a la BD de biometri_fenix.
 * 
 */
package pkgIfx_Conexiones;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.DefaultComboBoxModel;
import javax.swing.DefaultListModel;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import pkg1ifxArchivos.cifxLogs;

/**
 *
 * @author Johan Agustin Bayona Camargo 3012328016
 */
public class cIfxBD_General {
//Variables Generales de la Clase     
    cifxLogs log;
    private final String url = "jdbc:mysql://";
    private final String serverName = "localhost";
    private final String portNumber = "3306";
    private final String databaseName = "biometria_fenix";
    private final String userName = "root";
    private final String password = "9912056";
    
    public cIfxBD_General(cifxLogs Log){
        log = Log;
    }
//metodo de conexion a la BD datos de local 
    private Connection conexion()throws Exception{
    Connection con = null;
        try {
            Class.forName("org.gjt.mm.mysql.Driver");
            DriverManager.setLoginTimeout(60);
            con = DriverManager.getConnection(url+serverName+":"+portNumber+"/"+databaseName, userName , password);
        }catch(SQLException ex1){
            log.outMensaje("Error SQLException cIfxCnx_DBGeneral, conexion mysql, "+ex1);
        }catch (ClassNotFoundException ex2){
            log.outMensaje("Error ClassNotFoundException cIfxCnx_DBGeneral, conexion mysql, "+ex2);
        }catch(Exception ex3){
            log.outMensaje("Error Exception cIfxCnx_DBGeneral, conexion mysql, "+ex3);
        }
        return con;
    }
// Numero de afiliados que se encuentran el la BD para ser destanqueados    
    public int numero_afiliados(){
        int num = 0;
        try {
            Connection con = conexion();
            PreparedStatement stmt = null;
            stmt = con.prepareStatement("SELECT COUNT(afi_identificacion) FROM afi_afiliado a;");
            ResultSet rs = stmt.executeQuery();
            rs.next();
            num = rs.getInt(1);
        } catch (Exception ex) {
            log.outMensaje("Error Exception cIfxCnx_DBGeneral, numero_afiliados, "+ex);
        }
        return num;
    }
//Validacion del usuario y contrasenia encriptados    
    public String[] validarusuario(String usuario, String Pasword) {
        String[] retorna= new String[6];
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT * FROM Usuarios where Usuario ='"+usuario+"' AND Password ='"+Pasword+"'");
            ResultSet rs2 = stmt2.executeQuery();
            while (rs2.next())
            {
                retorna[0] = rs2.getString(4);
                retorna[1] = rs2.getString(5);
                retorna[2] = rs2.getString(7);
                retorna[3] = rs2.getString(6);
                retorna[4] = rs2.getString(8);
                retorna[5] = rs2.getString(9);
            }
            rs2.close();
            con.close();
        }
        catch (Exception e) {
            log.outMensaje("Error validarusuario cIfxCnx_DBGeneral, "+e);
        }
        return retorna;
    }
//Trae la lista de departamentos    
    public DefaultComboBoxModel Departaentos()
    {
        DefaultComboBoxModel departamentos = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        departamentos.addElement("Sin selección");
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT DPT_NOMDEPARTAMENTO FROM Departamentos ORDER BY DPT_NOMDEPARTAMENTO ASC");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                departamentos.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        }
        catch (Exception e)
        {
            log.outMensaje("Error Departaentos cIfxCnx_DBGeneral: "+e);
            return departamentos;
        }
        return departamentos;
    }
//Trae la lista de municipios segun el departamento    
    public DefaultComboBoxModel Municipios(String DPTO){
        String dd = null ;
        DefaultComboBoxModel municipios = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        municipios.addElement("Sin selección");
//        if(DPTO.length()<2)
//            DPTO = "0"+DPTO;
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT DPT_CODDPTO FROM Departamentos WHERE DPT_NOMDEPARTAMENTO = '"+DPTO+"'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next())
                dd = rs.getString(1);
            stmt = con.prepareStatement("SELECT CIU_NOMCIUDAD FROM Ciudades WHERE CIU_CODDPTO = '" +dd+"' ORDER BY CIU_NOMCIUDAD ASC");
            rs = stmt.executeQuery();
            while (rs.next()) {
                municipios.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            log.outMensaje("Error Municipios cIfxCnx_DBGeneral: "+e);
            return municipios;
        }
        return municipios;
    }
//Trae un modelo con las fuerza, segun el codigo asignado PAR_TABLA en catalogos    
    public DefaultComboBoxModel fuerzas(String fuerza){
        DefaultComboBoxModel fuerzacmodel = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        fuerzacmodel.addElement("Sin selección");
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT nombre FROM fuerzas WHERE prm_evaluable = "+fuerza+" ORDER BY nombre ASC");
            ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                fuerzacmodel.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            log.outMensaje("Error fuerzas cIfxCnx_DBGeneral: "+e);
            return fuerzacmodel;
        }
        return fuerzacmodel;
    }
    //Trae un modelo con los tipos de Documentos, segun el codigo asignado PAR_TABLA en catalogos
    public DefaultComboBoxModel TipDoc(String tipdoc){
        DefaultComboBoxModel fuerzacmodel = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        fuerzacmodel.addElement("Sin selección");
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT par_desc FROM catalogo WHERE par_tabla = "+tipdoc+" ORDER BY par_desc ASC");
            ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                fuerzacmodel.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            log.outMensaje("Error Modelo TipDocm cIfxCnx_DBGeneral: "+e);
            return fuerzacmodel;
        }
        return fuerzacmodel;
    }
    //Trae un modelo con los perfiles, segun el codigo asignado PAR_TABLA en catalogos
    public DefaultComboBoxModel Perfil(String perfil){
        DefaultComboBoxModel fuerzacmodel = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        fuerzacmodel.addElement("Sin selección");
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT par_desc FROM catalogo WHERE par_tabla = "+perfil+" ORDER BY par_desc ASC");
            ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                fuerzacmodel.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            log.outMensaje("Error fuerzas cIfxCnx_DBGeneral: "+e);
            return fuerzacmodel;
        }
        return fuerzacmodel;
    }
//Trae un modelo con los sexos, segun el codigo asignado PAR_TABLA en catalogos
    public DefaultComboBoxModel Sexo(String sexo){
        DefaultComboBoxModel fuerzacmodel = new DefaultComboBoxModel();
        PreparedStatement stmt = null; //statement para el sql
        fuerzacmodel.addElement("Sin selección");
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT par_desc FROM catalogo WHERE par_tabla = "+sexo+" ORDER BY par_desc ASC");
            ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                fuerzacmodel.addElement(rs.getString(1));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            log.outMensaje("Error fuerzas cIfxCnx_DBGeneral: "+e);
            return fuerzacmodel;
        }
        return fuerzacmodel;
    }
//Obtener Tipo de Documento de catalogo desde el codigo    
    public String getTipDoc(String tipdoc) {
        String retorna ="";
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT PAR_DESC FROM Catalogo where PAR_VALOR ='"+tipdoc+"' AND PAR_TABLA ='8'");
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            retorna = rs2.getString(1);
            rs2.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error tipo documento 8: "+e);
            return retorna;
        }
        return retorna;
    }
//Obtener Tipo de Documento de catalogo desde el codigo
    public String getPerf(String perfil) {
        String retorna ="";
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT PAR_DESC FROM Catalogo where PAR_VALOR ='"+perfil+"' AND PAR_TABLA ='10'");
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            retorna = rs2.getString(1);
            rs2.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error perfil 10: "+e);
            return retorna;
        }
        return retorna;
    }
//Obtener Sexo correspondiente al codigo
    public String getSex(String sexo) {
        String retorna = "";
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT PAR_DESC FROM Catalogo where PAR_VALOR ='"+sexo+"' AND PAR_TABLA ='4'");
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            retorna = rs2.getString(1);
            rs2.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error tipo documento: "+e);
            return retorna;
        }
        return retorna;
    }
//Obtener Departamento de catalogo desde el codigo
    public String getDep(String Depart)
    {
        String retorna ="";
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT DPT_NOMDEPARTAMENTO FROM Departamentos where DPT_CODDPTO ='"+Depart+"'");
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            retorna = rs2.getString(1);
            rs2.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error tipo documento: "+e);
            return retorna;
        }
        return retorna;
    }
//Obtener la ciudad de catalogos desde el codigo    
    public String getCiu(String Ciud,String Dept) {
        String retorna ="";
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT CIU_NOMCIUDAD FROM Ciudades where CIU_CODDPTO ='"+Dept+"' AND CIU_CODCIUDAD ='"+Ciud+"'");
            ResultSet rs2 = stmt2.executeQuery();
            while (rs2.next()){
            retorna = rs2.getString(1);
        }
        rs2.close();
        con.close();
        } catch (Exception e) {
            System.out.println("Error Ciudad: "+e);
            return retorna;
        }
        return retorna;
    }
//Trae los datos para la conexion con el servidor    
    public String[] cargar(){
        String[] data= new String[5];
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT * FROM Con_Server");
            ResultSet rs2 = stmt2.executeQuery();
            while(rs2.next())
            {
                data[0] = rs2.getString(1);
                data[1] = rs2.getString(2);
                data[2] = rs2.getString(3);
                data[3] = rs2.getString(4);
                data[4] = rs2.getString(5);
            }
            rs2.close();
            con.close();
        }
        catch (Exception e) {
            log.outMensaje("Error cargar cIfxCnx_DBGeneral, trallendo parametros de conexion con el servidor: "+e);
            return data;
        }
        return data;
    }
//Trae el nombre o IP del servidor donde se encuantran almacenados los Repositorios
    public String NombServidor(){
        String data = null;
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT Nombre_ServidorRepos FROM rutas r;");
            ResultSet rs2 = stmt2.executeQuery();
            rs2.next();
            data = rs2.getString(1);
            rs2.close();
            con.close();
        }
        catch (Exception e) {
            log.outMensaje("Error cargar cIfxCnx_DBGeneral, trallendo parametros de conexion con el servidor: "+e);
            return data;
        }
        return data;
    }
//Trae la ruta correspondiente al repositorio a consultar S1 Reposervidor1, S2 Reposervidor3, L Repolocal      
    public String busruta(String bruta){
        String rutalocal,rutaservidor,rutaservidor_2,Nombreservidor,ruta = null;
        PreparedStatement stmt2;
        try{
            
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT * FROM rutas");
            ResultSet rs1 = stmt2.executeQuery();
            rs1.next();
            rutalocal = rs1.getString(1);
            rutaservidor = rs1.getString(2);
            rutaservidor_2 = rs1.getString(3);
            Nombreservidor = rs1.getString(5);
            rs1.close();
            con.close();
        }
        catch(Exception e){
            log.outMensaje("Error busruta cIfxCnx_DBGeneral: "+e);
            return null;
        }
        if(bruta.equals("S1")){
            ruta = rutaservidor;
        }
        if(bruta.equals("L")){
            ruta = rutalocal;
        }
        if(bruta.equals("S2")){
            ruta = rutaservidor_2;
        }
        if(bruta.equals("NS")){
            ruta = Nombreservidor;
        }
        return ruta;
    }
//Validar usuario local 
    public boolean Validar_Usuario(String cedula){
        int id = 0;
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT Id FROM usuarios where Identificacion = '"+cedula+"'");
            ResultSet rs1 = stmt.executeQuery();
            rs1.next();
            id = rs1.getInt(1);
            rs1.close();
            con.close();
        }
        catch (Exception e) {
            return false;
        }
        if(id == 0) return false;   //no existe
        else return true;           //si existe
    }
//Insertar usuario local 
    public boolean Insertar_Usuario(String usuario, String paswor, int rool, String p_nombre, String P_Apellido, 
            String s_nombre, String S_Apellido, String Cedula, String FechCrea,String UsFenix) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("INSERT INTO Usuarios(Usuario,Password,Rool,P_Nombre_Usuario,"
                    + "P_Apellido_Usuario,S_Nombre_Usuario,S_Apellirdo_Usuario, Identificacion,"
                    + "Fecha_Creacion,Creado_Por)"+ "VALUES ('"+usuario+"','"+paswor+"','"+rool+"','"+p_nombre+
            "','"+P_Apellido+"'"+ ",'"+s_nombre+"','"+S_Apellido+"','"+Cedula+"','"+FechCrea+"','"+UsFenix+"')");
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error el usuario no a sido creado\n\nReporte : \n"+e);
            log.outMensaje("Error Insertar_Usuario cIfxCnx_DBGeneral: "+e);
            return false;
        }
        return true;
    }
//Consultar usuario en BD     
    public String[] ConsultUsuario(String cedula) {
        String[] data = new String[9];
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT * FROM usuarios where Identificacion = '"+cedula+"'");
            ResultSet rs1 = stmt.executeQuery();
            rs1.next();
            data[0] = rs1.getString(4);
            data[1] = rs1.getString(5);
            data[2] = rs1.getString(7);
            data[3] = rs1.getString(6);
            data[4] = rs1.getString(8);
            data[5] = rs1.getString(10);
            data[6] = rs1.getString(11);
            data[7] = rs1.getString(12);
            data[8] = rs1.getString(13);
            rs1.close();
            con.close();
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, "El Documento de identidad no existe en la BD Local.\nIntente un documento valido. : \n");
        }
        return data;
    }
//Consultar Movimientos en BD en la tabla usuario     
    public DefaultListModel MovUsuarioCed(String Fecha) {
        DefaultListModel modelo = new DefaultListModel();  
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT * FROM usuarios where Fecha_Creacion = '"+Fecha+"'");
            ResultSet rs1 = stmt.executeQuery();
            while (rs1.next())
            {
                modelo.addElement(rs1.getString(9));
            }
            stmt = con.prepareStatement("SELECT * FROM usuarios where Fecha_Edicion = '"+Fecha+"'");
            rs1 = stmt.executeQuery();
            while (rs1.next())
            {
                modelo.addElement(rs1.getString(9));
            }
            rs1.close();
            con.close();
        }catch (Exception e) {
        }
        return modelo;
    }
//Consultar Movimientos en BD en la tabla usuario     
    public DefaultListModel MovUsuarioNom(String Fecha) {
        DefaultListModel modelo = new DefaultListModel();  
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT * FROM usuarios where Fecha_Creacion = '"+Fecha+"'");
            ResultSet rs1 = stmt.executeQuery();
            while (rs1.next())
            {
                modelo.addElement("  C - "+rs1.getString(5)+" "+rs1.getString(6));
            }
            stmt = con.prepareStatement("SELECT * FROM usuarios where Fecha_Edicion = '"+Fecha+"'");
            rs1 = stmt.executeQuery();
            while (rs1.next())
            {
                modelo.addElement("  E - "+rs1.getString(5)+" "+rs1.getString(6));
            }
            rs1.close();
            con.close();
        }catch (Exception e) {
        }
        return modelo;
    }    
//Eliminar usuario de BD
    public boolean Eliminar_Usuario(String usuario) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("DELETE FROM usuarios WHERE Identificacion = "+usuario);
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error el uuario no a sido creado\n\nReporte : \n"+e);
            log.outMensaje("Error Insertar_Usuario cIfxCnx_DBGeneral: "+e);
            return false;
        }
        return true;
    }
//Actualizar usuario local 
    public boolean ActUsuario(String paswor, int rool, String cedula,String FechaMod,String Usuario) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("UPDATE Usuarios set Password = "
                    + "'"+paswor+"',Rool = '"+rool+"',Fecha_Edicion = '"+FechaMod+"',Editado_Por = '"+Usuario+"' where Identificacion = "+cedula);
            stmt.executeUpdate();
            con.close();
        }catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error el uuario no a sido actualizado\n\nReporte : \n"+e);
            log.outMensaje("Error ActUsuario cIfxCnx_DBGeneral: "+e);
            return false;
        }
        return true;
    }
//Actualiza parametros de conexion a la BD del Servidor    
    public void ActualPar_conexion(String nom, String N_Servidor, String Puerto, String BD, String Usuario, String Contraseña) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("UPDATE Con_Server SET Nombre_Servidor = '"+N_Servidor+"',Puerto = '"+Puerto+
            "',Nombre_BD= '"+BD+"',Usuario = '"+Usuario+"',Pasword = '"+Contraseña+"' WHERE Nombre_Servidor = '"+nom+"'");
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            log.outMensaje("Error Update Actualizar_conexion Servidor : "+e);
        }
    } 
//Actualiza parametros de Repositorio Servidor y Local       
    public void Actualizar_Ruta_Repositorios(String Nomb_Servidor,String Ruta_local,String Ruta_servidor,String Ruta_servidor_2) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("UPDATE rutas SET Nombre_ServidorRepos = '"+Nomb_Servidor+"',ruta_local = '"+Ruta_local+"', ruta_servidor_1 = '"+Ruta_servidor+"', ruta_servidor_2 = '"+Ruta_servidor_2+"' WHERE id = '1'");
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            log.outMensaje("Error Update Actualizar_conexion Servidor : "+e);
        }
     }
//Consulta el afiliado en la Base de Datos local    
     public String[] ConsultarAfiliado(String cedulaBus) throws SQLException {
        String consulta[] = new String[20];
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("SELECT * FROM afi_afiliado where afi_identificacion ='"+cedulaBus+"'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next())
                for (int row = 0; row <= 19; row++)
                    consulta[row] = rs.getString(row+1);
            rs.close();
            con.close();
            }
        catch (Exception e) {
            log.outMensaje("Error en consulta de cedula BD: "+e);
        }
        return consulta;
    }
//Consulta en catalogos el departamento correspondiente al cod     
     public String CodString_Departamento (String select){
        PreparedStatement stmt4;
        String str = "" ;
        try{
            Connection con = conexion();
            stmt4 = con.prepareStatement("SELECT DPT_CODDPTO FROM Departamentos WHERE DPT_NOMDEPARTAMENTO = '"+select+"'");
            ResultSet rs3 = stmt4.executeQuery();
            rs3.next();
            str = rs3.getString(1).toString();
            rs3.close();
            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
        return str;
    }
//Consulta en catslogos la ciudad correspondiente al cod     
    public String CodString_Ciudades (String select, String dep){
        PreparedStatement stmt3;
        String str = "" ;
        try{
            Connection con = conexion();
            stmt3 = con.prepareStatement("SELECT CIU_CODCIUDAD FROM Ciudades WHERE CIU_NOMCIUDAD = '"+select+"'"+"and CIU_CODDPTO = '"+dep+"'");
            ResultSet rs2 = stmt3.executeQuery();
            while (rs2.next()) {
                str = rs2.getString(1).toString();}
            rs2.close();
            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
        return str;
    }
//Insertar nuevo afiliado a la base de datos local    
    public int NuevAfiliado(String cedula,int tipo_documento,String p_apellido,String s_apellido,String p_nombre,
            String s_nombre,String fechanac,String ciudad_nacimiento,String dpto_nacimiento,
            String fecha_expedicion,String ciudad_expedicion,String dpto_expedicion,int sexo, int perfil)
    {       
        if(fechanac != null) fechanac = "'"+fechanac+"'";
        if(fecha_expedicion  != null) fecha_expedicion = "'"+fecha_expedicion+"'";
        try {
            Connection con = conexion();
            PreparedStatement stmt = null;
            stmt = con.prepareStatement("INSERT INTO afi_afiliado(afi_identificacion, cat_tipo_id, afi_primer_apellido,"
            + "afi_segundo_apellido, afi_primer_nombre, afi_segundo_nombre, afi_fecha_nacimiento, cat_afi_ciudad_nacimiento"
            +", cat_afi_dpto_nacimiento, afi_fecha_expedicion_documento, cat_afi_ciudad_expedicion_documento,"
            + "cat_afi_dpto_expedicion_documento, cat_afi_sexo, afi_perfil) VALUES ("
            +cedula+","+tipo_documento+",'"+p_apellido+"','"+s_apellido+"','"+p_nombre+"','"
            +s_nombre+"',"+fechanac+",'"+ciudad_nacimiento+"','"+dpto_nacimiento+"',"
            +fecha_expedicion+",'"+ciudad_expedicion+"','"+dpto_expedicion+"',"+sexo+","+perfil+")");
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Problemas con la Base de Datos local","BD Local\n\n"+e, JOptionPane.ERROR_MESSAGE);
            return 0;
        }
        return 1;
    }
//Insertar movimiento en Biocapturainformacion en la base de datos local        
    public void NuevoRegistro(String cedula_usuario, String nombre_usuario,String departamento, String municipio, String unidad, String fuerza, java.sql.Date fechaenrolamiento,
            String firma, String cedula, String huella, String rostro, String iris, String equipo, String afi_cedula)
    {
        try {
            Connection con = conexion();
            PreparedStatement stmt = null;
            stmt = con.prepareStatement("INSERT INTO biocaptura_informacion (BIO_USUARIO_ID, BIO_NOMBRE_USUARIO, BIO_DEPARTAMENTO,"
            + "BIO_MUNICIPIO, BIO_UNIDAD, BIO_FUERZA, BIO_AFI_FECHA_ENROLAMIENTO, BIO_FIRMA"
            +", BIO_CEDULA, BIO_HUELLA, BIO_ROSTRO, BIO_IRIS, BIO_EQUIPO,BIO_AFI_IDENTIFICACION) VALUES ("
            +cedula_usuario+",'"+nombre_usuario+"','"+departamento+"','"+municipio+"','"+unidad+"','"
            +fuerza+"'"+",'"+fechaenrolamiento+"','"+firma+"','"+cedula+"','"
            +huella+"','"+rostro+"','"+iris+"','"+equipo+"','"+afi_cedula+"')");
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            System.out.println("append "+e);
        }
    }
    
    public String[] Consultar_bio_informacion(String cedula) {
        String[] retorna = new String[15];
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT * FROM biocaptura_informacion WHERE BIO_AFI_IDENTIFICACION = "+cedula+" ORDER BY BIO_INFORMACION_ID DESC LIMIT 1");
            ResultSet rs2 = stmt2.executeQuery();
            while (rs2.next())
            {
                retorna[0] = String.valueOf(rs2.getInt(2));
                retorna[1] = rs2.getString(3);
                retorna[2] = rs2.getString(4);
                retorna[3] = rs2.getString(5);
                retorna[4] = rs2.getString(6);
                retorna[5] = rs2.getString(7);
                retorna[6] = rs2.getString(8);
                retorna[7] = rs2.getString(9);
                retorna[8] = rs2.getString(10);
                retorna[9] = rs2.getString(11);
                retorna[10] = rs2.getString(12);
                retorna[11] = rs2.getString(13);
                retorna[12] = rs2.getString(14);
                //retorna[13] = String.valueOf(rs2.getInt(15));
            }
            rs2.close();
            con.close();
        }
        catch (Exception e) {
            System.out.println("clase recordDBPersona, buscar_bio_informacion error : "+e);
            return retorna;
        }
        return retorna;
    }
//Eliminar afiliado de la BD Local    
    public void Eliminar_afiliado(String Cedula_Id){
        PreparedStatement stmt = null; //statement para el sql
        try{
            Connection con = conexion();
            stmt = con.prepareStatement("DELETE FROM afi_afiliado where afi_identificacion ='"+Cedula_Id+"'");
            stmt.executeUpdate();
            con.close();
        }catch (Exception e){
            System.out.println("Error en base de datos: "+e);
        }
    }
//Eliminar Registro de Biocaptura Informacion de la BD Local    
    public void Eliminar_Bio_Informacion(String Cedula_Id){
        PreparedStatement stmt = null; //statement para el sql
        try{
            Connection con = conexion();
            stmt = con.prepareStatement("DELETE FROM biocaptura_informacion where BIO_AFI_IDENTIFICACION ='"+Cedula_Id+"'");
            stmt.executeUpdate();
            con.close();
        }catch (Exception e){
            System.out.println("Error en base de datos: "+e);
        }
    }
//Recoje el ID correspondiente al registro generado en Biocaptura Informacion al comenzar el enrolamiento    
    public int ID_Bio_Informacion(String cedula) {
        int retorna = 0;
        PreparedStatement stmt2 = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt2 = con.prepareStatement("SELECT * FROM biocaptura_informacion WHERE BIO_AFI_IDENTIFICACION = "+cedula+" ORDER BY BIO_INFORMACION_ID DESC LIMIT 1");
            ResultSet rs2 = stmt2.executeQuery();
            while (rs2.next())
            {
                retorna = rs2.getInt(1);
            }
            rs2.close();
            con.close();
        }
        catch (Exception e) {
            System.out.println("clase recordDBPersona, validarusuario: "+e);
            return retorna;
        }
        return retorna;
    }
//Actualiza la informacion de Biocaptura Informacion con los biometricos capturados    
    public void Act_BioInformacion(String firma, String cedula, String huella, String rostro, String iris, int ID) {
        PreparedStatement stmt = null; //statement para el sql
        try {
            Connection con = conexion();
            stmt = con.prepareStatement("UPDATE biocaptura_informacion SET BIO_FIRMA = '"+firma+"', BIO_CEDULA = '"+cedula+"', BIO_HUELLA = '"+huella+"', BIO_ROSTRO = '"+rostro+"', BIO_IRIS = '"+iris+"' WHERE BIO_INFORMACION_ID = "+ ID);
            stmt.executeUpdate();
            con.close();
        }
        catch (Exception e) {
            System.out.println("Error Update actualizar_informacion Servidor : "+e);
        }
    }
    
    public DefaultTableModel Consultar_Destanqueo () throws Exception{
        DefaultTableModel modelo = new DefaultTableModel();
        modelo.addColumn(" No. ");
        modelo.addColumn("Cedula");
        modelo.addColumn("Primer Apellido");
        modelo.addColumn("Segundo Apellido");
        modelo.addColumn("Primer Nombre");
        modelo.addColumn("Segundo Nombre");
        PreparedStatement stmt3;
        Connection con = conexion();
        PreparedStatement pstmt;
            ResultSet rs;
            pstmt = con.prepareStatement("Select MAX(afi_afiliados_id) as maximo from afi_afiliado");
            rs = pstmt.executeQuery();
            rs.next();
            int maxID = rs.getInt(1);
        Object[] destaqueo = new Object[20];
        try{
            stmt3 = con.prepareStatement("SELECT * FROM biometria_fenix.afi_afiliado");
            ResultSet rs2 = stmt3.executeQuery();
            int colum=0;
            while (rs2.next()) {
                    destaqueo[0] = colum+1;
                    destaqueo[1] = rs2.getString(2);
                    destaqueo[2] = rs2.getString(4);
                    destaqueo[3] = rs2.getString(5);
                    destaqueo[4] = rs2.getString(6);
                    destaqueo[5] = rs2.getString(7);
                    colum = colum+1;
                modelo.addRow(destaqueo);
            }
            rs2.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona, Destanqueo, error : "+e);
        }
        return modelo;
    }
//trae un modela de tabla con toda la informacion de afi afiliados que hay para destanquear
    public DefaultTableModel consulta_destanqueo () throws Exception{
        DefaultTableModel modelo = new DefaultTableModel();
        modelo.addColumn(" No. ");
        modelo.addColumn("Cedula");
        modelo.addColumn("Primer Apellido");
        modelo.addColumn("Segundo Apellido");
        modelo.addColumn("Primer Nombre");
        modelo.addColumn("Segundo Nombre");
        PreparedStatement stmt3;
        Connection con = conexion();
        PreparedStatement pstmt;
            ResultSet rs;
            pstmt = con.prepareStatement("Select MAX(afi_afiliados_id) as maximo from afi_afiliado");
            rs = pstmt.executeQuery();
            rs.next();
        Object[] destaqueo = new Object[20];
        try{
            stmt3 = con.prepareStatement("SELECT * FROM biometria_fenix.afi_afiliado");
            ResultSet rs2 = stmt3.executeQuery();
            int colum=0;
            while (rs2.next()) {
                    destaqueo[0] = colum+1;
                    destaqueo[1] = rs2.getString(2);
                    destaqueo[2] = rs2.getString(4);
                    destaqueo[3] = rs2.getString(5);
                    destaqueo[4] = rs2.getString(6);
                    destaqueo[5] = rs2.getString(7);
                    colum = colum+1;
                modelo.addRow(destaqueo);
            }
            rs2.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona, Destanqueo, error : "+e);
        }
        return modelo;
    }

    public DefaultTableModel BitacoraReporte (String FECHAINICIO, String FECHAFINAL) throws Exception{
        DefaultTableModel modelo = new DefaultTableModel();
        modelo.addColumn("Unidad");
        modelo.addColumn("Biometrias");
        modelo.addColumn("Firma");
        modelo.addColumn("Huella");
        modelo.addColumn("Foto");
        modelo.addColumn("Iris");
        modelo.addColumn("Escaneo");
        PreparedStatement stmt1, stmt2, stmt3, stmt4, stmt5, stmt6, stmt7;
        ResultSet rs1 = null, rs2 = null, rs3 = null, rs4 = null, rs5 = null, rs6 = null, rs7 = null;
        Connection con = conexion();
        Object[] destaqueo = new Object[7];
        try{
            stmt1 = con.prepareStatement("SELECT DISTINCT (BIO_UNIDAD) FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"' ORDER BY BIO_INFORMACION_ID DESC");
            rs1 = stmt1.executeQuery();
            while (rs1.next()) {
                destaqueo[0] = rs1.getString(1);

                stmt2 = con.prepareStatement("SELECT Count(BIO_UNIDAD) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                rs2 = stmt2.executeQuery();
                rs2.next();
                    destaqueo[1] = rs2.getString(1);

                    stmt3 = con.prepareStatement("SELECT Count(BIO_FIRMA) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_FIRMA = 'SI' AND BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                    rs3 = stmt3.executeQuery();
                    rs3.next();
                        destaqueo[2] = rs3.getString(1);

                    stmt4 = con.prepareStatement("SELECT Count(BIO_HUELLA) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_HUELLA = 'SI' AND BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                    rs4 = stmt4.executeQuery();
                    rs4.next();
                        destaqueo[3] = rs4.getString(1);

                    stmt5 = con.prepareStatement("SELECT Count(BIO_ROSTRO) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_ROSTRO = 'SI' AND BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                    rs5 = stmt5.executeQuery();
                    rs5.next();
                        destaqueo[4] = rs5.getString(1);

                    stmt6 = con.prepareStatement("SELECT Count(BIO_IRIS) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_IRIS = 'SI' AND BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                    rs6 = stmt6.executeQuery();
                    rs6.next();
                        destaqueo[5] = rs6.getString(1);

                    stmt7 = con.prepareStatement("SELECT Count(BIO_CEDULA) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_CEDULA = 'SI' AND BIO_UNIDAD = '"+destaqueo[0]+"' AND BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"'");
                    rs7 = stmt7.executeQuery();
                    rs7.next();
                        destaqueo[6] = rs7.getString(1);

                modelo.addRow(destaqueo);
            }
            rs1.close();
            rs2.close();
            rs3.close();
            rs4.close();
            rs5.close();
            rs6.close();
            rs7.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona, Consulta bitacora, error : "+e);
        }
        return modelo;
    }
        public DefaultTableModel infdetallado(String fecha, JLabel N_Enrolados, JLabel N_Completas, JLabel N_Completar, JLabel N_SinBiometrias) throws Exception{
        DefaultTableModel modelo = new DefaultTableModel();
        modelo.addColumn("cedula");
        modelo.addColumn("Afiliado");
        modelo.addColumn("Bio");
        modelo.addColumn("Lugar");
        modelo.addColumn("Fuerza");
        modelo.addColumn("Equipo");
        modelo.addColumn("Funcionario");
        PreparedStatement stmt3, stmt4;
        Connection con = conexion();
        Object[] destaqueo = new Object[20];
        try{
            stmt3 = con.prepareStatement("SELECT * FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO = '"+fecha+"'");
            ResultSet rs2 = stmt3.executeQuery();
            int bimetric = 0, num = 0, complet = 0, sinbio = 0;
            while (rs2.next()) {
                    num = num+1;
                    bimetric = 0;
                    destaqueo[0] = rs2.getString(15);
                    if(rs2.getString(10).equals("SI")){
                        bimetric = bimetric+1;
                    }if(rs2.getString(9).equals("SI")){
                        bimetric = bimetric+1;
                    }if(rs2.getString(12).equals("SI")){
                        bimetric = bimetric+1;
                    }if(rs2.getString(11).equals("SI")){
                        bimetric = bimetric+1;
                    }if(rs2.getString(13).equals("SI")){
                        bimetric = bimetric+1;
                    }

                    if(bimetric == 5){
                        complet = complet+1;
                    }if(bimetric == 0){
                        sinbio = sinbio+1;
                    }
                    destaqueo[2] = ""+bimetric;
                    destaqueo[3] = rs2.getString(4)+", "+rs2.getString(5);
                    destaqueo[5] = rs2.getString(14);
                    destaqueo[4] = rs2.getString(7);
                    destaqueo[6] = rs2.getString(2);
                    stmt4 = con.prepareStatement("SELECT afi_primer_apellido, afi_primer_nombre FROM biometria_fenix.afi_afiliado WHERE afi_identificacion ="+destaqueo[0]);
                    ResultSet rs3 = stmt4.executeQuery();
                    rs3.next();
                        destaqueo[1] = rs3.getString(1)+", "+rs3.getString(2);
                modelo.addRow(destaqueo);
            }
            N_Enrolados.setText(""+num);
            N_Completas.setText(""+complet);
            N_SinBiometrias.setText(""+sinbio);
            int completarm = num-complet;
            N_Completar.setText(""+completarm);
            rs2.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona, infdetallado, error : "+e);
        }
        return modelo;
    }

    public DefaultTableModel consulta_bitacora (String FECHAINICIO, String FECHAFINAL, JLabel Total_Enrolados) throws Exception{
        DefaultTableModel modelo = new DefaultTableModel();
        modelo.addColumn("N");
        modelo.addColumn("Fecha");
        modelo.addColumn("NE");
        modelo.addColumn("Funcionarios");
        modelo.addColumn("Fuerzas");
        PreparedStatement stmt3, stmt4, stmt5, stmt6;
        ResultSet rs2 = null, rs3 = null, rs4 = null, rs5 = null;
        Connection con = conexion();
        Object[] destaqueo = new Object[5];
        int numero = 0;
        int enrr = 0;
        try{
            stmt3 = con.prepareStatement("SELECT DISTINCT (BIO_AFI_FECHA_ENROLAMIENTO) FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO BETWEEN '"+FECHAINICIO+"' AND '"+FECHAFINAL+"' ORDER BY BIO_INFORMACION_ID DESC");
            rs2 = stmt3.executeQuery();
            while (rs2.next()) {
                numero = numero+1;
                destaqueo[0] = numero;
                destaqueo[3] = "";
                destaqueo[4] = "";
                destaqueo[1] = rs2.getString(1);
                stmt4 = con.prepareStatement("SELECT Count(BIO_AFI_FECHA_ENROLAMIENTO) AS VALOR FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO = '"+destaqueo[1]+"'");
                rs3 = stmt4.executeQuery();
                rs3.next();
                    destaqueo[2] = rs3.getString(1);
                    enrr = enrr + rs3.getInt(1);
                    System.out.println("N0.enroladod: "+enrr);
                    stmt5 = con.prepareStatement("SELECT DISTINCT (BIO_NOMBRE_USUARIO) FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO = '"+destaqueo[1]+"'");
                    rs4 = stmt5.executeQuery();
                    while (rs4.next()) {
                        if(destaqueo[3].equals("")) destaqueo[3] = rs4.getString(1);
                        else destaqueo[3] = destaqueo[3]+", "+rs4.getString(1);
                    }
                    stmt6 = con.prepareStatement("SELECT DISTINCT (BIO_FUERZA) FROM biometria_fenix.biocaptura_informacion WHERE BIO_AFI_FECHA_ENROLAMIENTO = '"+destaqueo[1]+"'");
                    rs5 = stmt6.executeQuery();
                    while (rs5.next()) {
                        if(destaqueo[4].equals("")) destaqueo[4] = rs5.getString(1);
                        else destaqueo[4] = destaqueo[4]+", "+rs5.getString(1);
                    }
                    Total_Enrolados.setText(""+enrr);
                modelo.addRow(destaqueo);
            }
            rs2.close();
            rs3.close();
            rs4.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona, Consulta bitacora, error : "+e);
        }
        return modelo;
    }

    public int Contador() throws Exception{
        int afiliados = 0;
        PreparedStatement stmt;
        ResultSet rs = null;
        Connection con = conexion();
        try{
            stmt = con.prepareStatement("SELECT count(afi_identificacion) FROM `biometria_fenix`.`afi_afiliado`");
            rs = stmt.executeQuery();
            rs.next();
                afiliados = rs.getInt(1);
            rs.close();
            con.close();
        }catch(SQLException e){
            System.out.println("RecorDBPersona,  contador, error : "+e);
        }
        return afiliados;
    }
}




